USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetAllUsersXml')
	DROP FUNCTION dbo.GetAllUsersXml
GO


CREATE FUNCTION [dbo].[GetAllUsersXml] ()
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result		= '<USERS><entry id="0"></entry>'

	select @result = @result + '<entry id="' + CAST(PartnerNo AS VARCHAR) + '"><![CDATA[' +  PartnerFirstName + ' ' +PartnerLastName + ']]></entry>'
	FROM TR_PARTNER 
	where PartnerCategory = 1 AND PartnerTYpe = 4 AND isActive = 1
	ORDER BY PartnerFirstName + ' ' +PartnerLastName
	set @result = @result + '</USERS>'	

	return @result

END